/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50141
Source Host           : localhost:3306
Source Database       : sport

Target Server Type    : MYSQL
Target Server Version : 50141
File Encoding         : 65001

Date: 2014-02-27 09:44:32
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `personals`
-- ----------------------------
DROP TABLE IF EXISTS `personals`;
CREATE TABLE `personals` (
`personalid`  int(11) NOT NULL AUTO_INCREMENT ,
`firstname`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`lastname`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`fullname`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`nickname`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`birthdate`  date NOT NULL ,
`place_of_birth`  char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`avatar`  text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'json' ,
`tag`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`short`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`description`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`created`  datetime NOT NULL ,
`modified`  datetime NOT NULL ,
`status`  tinyint(4) NOT NULL ,
`type`  smallint(6) NOT NULL DEFAULT 0 COMMENT 'b0:player;b1:assistant manager;b2:manager;b3:ceo;b4:vice president;b5:president' ,
`features`  text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`logid`  int(11) NOT NULL ,
PRIMARY KEY (`personalid`),
INDEX `IX_status` USING BTREE (`status`) ,
INDEX `IX_created` USING BTREE (`created`) ,
FULLTEXT INDEX `FT_fullname_tag_short` (`fullname`, `tag`, `short`) 
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of personals
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `personals_nationality`
-- ----------------------------
DROP TABLE IF EXISTS `personals_nationality`;
CREATE TABLE `personals_nationality` (
`id`  int(11) NOT NULL ,
`personalid`  int(11) NOT NULL ,
`national`  char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`),
UNIQUE INDEX `IX_personalid_nationality` USING BTREE (`personalid`, `national`) 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of personals_nationality
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Procedure structure for `personals_add`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_add`;

CREATE PROCEDURE `personals_add`(ploginid int,pfirstname varchar(50),plastname varchar(50),pnickname varchar(100),pbirthdate date,pplace_of_birth char(2),pavatar varchar(255),ptag varchar(100),pshort varchar(255),pdescription text,ptype smallint(6),pfeatures text)
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rPersonalId INT DEFAULT 0;
	DECLARE rFullName VARCHAR(100) DEFAULT NULL;
	IF rErrCode=0 AND _admin_check_permission(1<<1,ploginid,'personal')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		SET rFullName = concat(pfirstname, ' ', plastname);
		INSERT personals(firstname, lastname, fullname, nickname, birthdate,
								place_of_birth, avatar, tag, short, description, 
								created, modified, status, type, logid)
		VALUE(pfirstname, lastname, rFullName, pnickname, pbirthdate, 
					pplace_of_birth, pavatar, ptag, pshort, pdescription,
					NOW(), NOW(), 1, ptype, ploginid);
		SET rPersonalId = LAST_INSERT_ID();	
	END IF;
	SELECT rErrCode AS errCode, rPersonalId AS personalid;
END;

-- ----------------------------
-- Procedure structure for `personals_delete`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_delete`;

CREATE PROCEDURE `personals_delete`(ploginid int(11),ppersonalid int(11))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rAvatar VARCHAR(255) DEFAULT NULL;
	DECLARE rType TINYINT(4) DEFAULT 0;
	DECLARE rPerm TINYINT(4) DEFAULT 0;
	SET rType = _admin_role(ploginid);
	SET rPerm = _admin_check_permission(1<<3,ploginid,'personal');
	IF rErrCode=0 AND (rPerm=0 OR rType < 5) THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		SELECT avatar INTO rAvatar FROM personals WHERE personalid=ppersonalid;
		DELETE FROM personals WHERE personalid=ppersonalid;
	END IF;
	SELECT rErrCode AS errCode, rAvatar AS avatar;
END;

-- ----------------------------
-- Procedure structure for `personals_get`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_get`;

CREATE PROCEDURE `personals_get`(ppersonalid int(11))
    READS SQL DATA
BEGIN	
		SELECT *
		FROM personals
		WHERE personalid=ppersonalid;
END;

-- ----------------------------
-- Procedure structure for `personals_getall`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_getall`;

CREATE PROCEDURE `personals_getall`(ploginid int,pstatus tinyint,poption tinyint(4)/**0:all*/,pkeyword varchar(50),pstart date,pend date,poffset int,plimit smallint(6))
    READS SQL DATA
BEGIN
	#call: 1,-1,0,'tu+khoa',null,null,0,10
	DECLARE rFoundRows INT DEFAULT 0;
	DECLARE rType TINYINT DEFAULT 0;
	DECLARE rStatus TINYINT DEFAULT 0;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmppersonals(personalid bigint);	
	# permission view
	SET rType = _admin_role(ploginid);
	IF rType < 3 THEN SET rStatus = 3;
	ELSE SET rStatus = rType + 1;	
	END IF;
	SET @sqlStm = CONCAT('
												INSERT tmppersonals(personalid)
												SELECT SQL_CALC_FOUND_ROWS
														personalid
												FROM personals
												WHERE personalid IS NOT NULL',	
													IF(pkeyword !='',
															IF(poption=0,
																 CONCAT(' AND MATCH(fullname,short,tag) AGAINST(',QUOTE(pkeyword),' IN BOOLEAN MODE)'),
																 ELT(poption,
																		CONCAT(' AND fullname LIKE ''%',pkeyword,'%'''),
																		CONCAT(' AND short LIKE ''%',pkeyword,'%'''),
																		CONCAT(' AND tag LIKE ''%',pkeyword,'%''')
																 )
															),
															''
													),
													IF(pstatus > -1, 
														CONCAT(' AND ABS(status)=',pstatus),
														CONCAT(' AND ABS(status)<',rStatus)
													),													
													IF(pstart <> '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(created,10) BETWEEN ''',pstart,''' AND ''',pend,''''),''),
													IF(pstart <> '0000-00-00' AND pend = '0000-00-00', CONCAT(' AND LEFT(created,10)=''',pstart,''''),''),
													IF(pstart = '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(created,10)=''',pend,''''),''),
												' ORDER BY personalid DESC
												LIMIT ',poffset,',',plimit
											);

	PREPARE stmt FROM @sqlStm;
	EXECUTE stmt;

	SET rFoundRows = FOUND_ROWS();
	
	SELECT SQL_SMALL_RESULT rFoundRows AS foundRows, B.*
	FROM tmppersonals A JOIN personals B ON A.personalid=B.personalid;

	DROP TEMPORARY TABLE IF EXISTS tmppersonals;	
END;

-- ----------------------------
-- Procedure structure for `personals_nationality_add`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_nationality_add`;

CREATE PROCEDURE `personals_nationality_add`(ploginid int(11),ppersonalid int(11),pnational char(2))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND 
		_admin_check_permission(1<<1,ploginid,'personal')=0 AND
		_admin_check_permission(1<<2,ploginid,'personal')=0
	THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 AND NOT EXISTS(SELECT 1 FROM personal_nationality WHERE personalid=ppersonalid AND national=pnational) THEN	
		INSERT personals_nationality(personalid,national)
		VALUE(ppersonalid, pnation);
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `personals_nationality_delete`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_nationality_delete`;

CREATE PROCEDURE `personals_nationality_delete`(ploginid int(11),ppersonalid int(11))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND 
		_admin_check_permission(1<<1,ploginid,'personal')=0 AND
		_admin_check_permission(1<<2,ploginid,'personal')=0
	THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN		
		DELETE FROM personals_nationality WHERE personalid=ppersonalid;
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `personals_update`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_update`;

CREATE PROCEDURE `personals_update`(ploginid int,ppersonalid int,pfirstname varchar(50),plastname varchar(50),pnickname varchar(100),pbirthdate date,pplace_of_birth char(2),pavatar varchar(255),ptag varchar(100),pshort varchar(255),pdescription text,ptype smallint(6),pfeatures text)
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rPersonalId INT DEFAULT 0;
	DECLARE rFullName VARCHAR(100) DEFAULT NULL;
	DECLARE rAvatar VARCHAR(255) DEFAULT NULL;
	IF rErrCode=0 AND _admin_check_permission(1<<2,ploginid,'personal')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		SET rFullName = concat(pfirstname, ' ', plastname);
		SET rAvatar = (SELECT avatar FROM personals WHERE personalid=ppersonalid);
		UPDATE personals
		SET firstname=pfirstname,
				lastname=plastname,
				fullname=rFullName,
				nickname=pnickname,
				birthdate=pbirthdate,
				place_of_birth=pplace_of_birth,
				avatar=IF(pavatar != '', pavatar, avatar),
				tag=ptag,
				short=pshort,
				description=pdescription,
				modified=NOW(),
				type=ptype,
				logid=ploginid
		WHERE personalid=ppersonalid;
	END IF;
	SELECT rErrCode AS errCode, rAvatar AS avatar;
END;

-- ----------------------------
-- Procedure structure for `personals_update_status`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_update_status`;

CREATE PROCEDURE `personals_update_status`(ploginid int(11),ppersonalid int(11),pstatus tinyint(4),pperm tinyint(4))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(pperm,ploginid,'personal')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode=0 THEN		
		UPDATE personals SET `status`=pstatus WHERE personalid=ppersonalid;	
	END IF;
	SELECT rErrCode AS errCode;
END;
DROP TRIGGER IF EXISTS `personals_on_delete_a`;

CREATE TRIGGER `personals_on_delete_a` AFTER DELETE ON `personals` FOR EACH ROW BEGIN
     DECLARE rPersonalId INT DEFAULT 0;
     SET rPersonalId = OLD.personalid;   
END;

-- ----------------------------
-- Auto increment value for `personals`
-- ----------------------------
ALTER TABLE `personals` AUTO_INCREMENT=1;
